--    Program: 		CAPAREXT_REMIT_TO_ADDRESS.sql
--    Description:  Extract AR Remit-To Address information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		   number;
   l_rejected_count 		   number;
   l_err_msg				   varchar2(2000);
                    		
   l_utl_file       		   utl_file.file_type;
   l_filename       		   varchar2(255);
   l_pathname       		   varchar2(100);
                    		
   l_ngo_sob				   varchar2(150);
   l_ngo_sob_id            	   number;
                    		  
   cursor cur_rrt (p_set_of_books_id number) is      
      select rrt.*  
      from   ra_remit_tos_all rrt,   
             hr_organization_information hoi 
      where  hoi.org_information_context = 'Operating Unit Information' 
      and    hoi.org_information3 = to_char(p_set_of_books_id)  
      and    hoi.organization_id = rrt.org_id;
  
begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_REMIT_TO_ADDRESS.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
  
   l_ngo_sob := '&1';
   
   begin
    select set_of_books_id
    into   l_ngo_sob_id
    from   gl_sets_of_books 
    where  upper(short_name) = upper(l_ngo_sob);
  exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
  end;

   for cur in cur_rrt(l_ngo_sob_id) loop   	
	  
  	  utl_file.put_line(l_utl_file, 
	   cur.ADDRESS_ID          || chr(9) ||
       cur.LAST_UPDATE_DATE    || chr(9) ||
       cur.LAST_UPDATED_BY     || chr(9) ||
       cur.CREATION_DATE       || chr(9) ||
       cur.CREATED_BY          || chr(9) ||
       cur.LAST_UPDATE_LOGIN   || chr(9) ||
       cur.STATUS              || chr(9) ||
       cur.COUNTRY             || chr(9) ||
       cur.STATE               || chr(9) ||
       cur.ATTRIBUTE_CATEGORY  || chr(9) ||
       cur.ATTRIBUTE1          || chr(9) ||
       cur.ATTRIBUTE2          || chr(9) ||
       cur.ATTRIBUTE3          || chr(9) ||
       cur.ATTRIBUTE4          || chr(9) ||
       cur.ATTRIBUTE5          || chr(9) ||
       cur.ATTRIBUTE6          || chr(9) ||
       cur.ATTRIBUTE7          || chr(9) ||
       cur.ATTRIBUTE8          || chr(9) ||
       cur.ATTRIBUTE9          || chr(9) ||
       cur.ATTRIBUTE10         || chr(9) ||
       cur.ATTRIBUTE11         || chr(9) ||
       cur.ATTRIBUTE12         || chr(9) ||
       cur.ATTRIBUTE13         || chr(9) ||
       cur.ATTRIBUTE14         || chr(9) ||
       cur.ATTRIBUTE15         || chr(9) ||
       cur.POSTAL_CODE_HIGH    || chr(9) ||
       cur.POSTAL_CODE_LOW     || chr(9) ||
       cur.ORG_ID   
	  );
   
	  l_processed_count := l_processed_count + 1;
   end loop;
   
   l_err_msg := '';
   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Remit To Address not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPAREXT_REMIT_TO_ADDRESS', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/     


--exit;
